package com.qst.repository;

import com.qst.pojo.entity.Order;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 订单数据访问层
 */
@Repository
public interface OrderRepository extends JpaRepository<Order, Integer> {
    
    /**
     * 根据站点ID查询订单（分页）
     */
    Page<Order> findByStationId(Integer stationId, Pageable pageable);
    
    /**
     * 根据站点ID和状态查询订单（分页）
     */
    Page<Order> findByStationIdAndStatus(Integer stationId, Order.OrderStatus status, Pageable pageable);
    
    /**
     * 根据客户ID查询订单（分页）
     */
    Page<Order> findByCustomerId(Integer customerId, Pageable pageable);
    
    /**
     * 根据客户ID查询所有订单（不分页）
     */
    List<Order> findByCustomerId(Integer customerId);
    
    /**
     * 根据客户ID和状态查询订单（分页）
     */
    Page<Order> findByCustomerIdAndStatus(Integer customerId, Order.OrderStatus status, Pageable pageable);
    
    /**
     * 统计站点在指定时间范围内的订单数量（按状态）
     */
    @Query("SELECT COUNT(o) FROM Order o WHERE o.stationId = :stationId AND o.createdAt >= :startTime AND o.createdAt < :endTime AND o.status = :status")
    Long countByStationIdAndTimeRangeAndStatus(@Param("stationId") Integer stationId, 
                                                 @Param("startTime") LocalDateTime startTime, 
                                                 @Param("endTime") LocalDateTime endTime, 
                                                 @Param("status") Order.OrderStatus status);
    
    /**
     * 统计站点在指定时间范围内已完成订单的总金额（按订单更新时间）
     */
    @Query("SELECT COALESCE(SUM(o.totalAmount), 0) FROM Order o WHERE o.stationId = :stationId AND o.updatedAt >= :startTime AND o.updatedAt < :endTime AND o.status = 'completed'")
    java.math.BigDecimal sumTotalAmountByStationIdAndTimeRange(@Param("stationId") Integer stationId, 
                                                                 @Param("startTime") LocalDateTime startTime, 
                                                                 @Param("endTime") LocalDateTime endTime);
    
    /**
     * 按状态统计站点订单数量（不限时间）
     */
    @Query("SELECT COUNT(o) FROM Order o WHERE o.stationId = :stationId AND o.status = :status")
    Long countByStationIdAndStatus(@Param("stationId") Integer stationId, @Param("status") Order.OrderStatus status);
    
    /**
     * 统计站点的所有订单数量
     */
    Long countByStationId(Integer stationId);
    
    /**
     * 按状态统计站点订单总金额
     */
    @Query("SELECT COALESCE(SUM(o.totalAmount), 0) FROM Order o WHERE o.stationId = :stationId AND o.status = :status")
    java.math.BigDecimal sumTotalAmountByStationIdAndStatus(@Param("stationId") Integer stationId, 
                                                              @Param("status") Order.OrderStatus status);
    
    /**
     * 统计在该站点下单的不同客户数量
     */
    @Query("SELECT COUNT(DISTINCT o.customerId) FROM Order o WHERE o.stationId = :stationId")
    Long countDistinctCustomersByStationId(@Param("stationId") Integer stationId);
    
    /**
     * 查询指定前缀的最大订单号（用于订单号生成器）
     */
    @Query(value = "SELECT order_no FROM orders WHERE order_no LIKE :prefix ORDER BY order_no DESC LIMIT 1", nativeQuery = true)
    String findMaxOrderNoByPrefix(@Param("prefix") String prefix);
}

